package com.wl.dzt.sys.core.dao.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;


/**
 * @author wl_code@163.com
 * @version 1.0
 * @date 2018/7/5/005
 * @description 数据库元数据实体
 * 用于储存coonetion和数据库连接信息
 */
@Component
public class DBMate {
    private static final Logger log = LoggerFactory.getLogger(DBMate.class);

    /**
     * 数据库连接
     */
    private Connection connection;
    /**
     * 数据库源信息
     */
    private DatabaseMetaData databaseMetaData;

    /**
     * 数据源
     */
    @Resource
    private DataSource dataSource;

    /**
     * <b>初始化</b>
     * <p>
     * 初始化
     * </p>
     */
    @PostConstruct
    public void init() {
        try {
            connection = dataSource.getConnection();
            databaseMetaData = connection.getMetaData();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * <b>获取某张表的源数据</b>
     * <p>
     * 根据一些参数信息获取表格
     * </p>
     *
     * @param tableName
     * @return DBTable
     * @throws SQLException
     */
    public DBTable getTable(String tableName) throws SQLException {
        ResultSet tableRet = databaseMetaData.getTables(null, "%", tableName,
                new String[]{"TABLE"});
        ResultSet foreinKey = databaseMetaData.getImportedKeys(null, null,
                tableName);
        Map<String, String> forekeys = new HashMap<String, String>();
        while (foreinKey.next()) {
            forekeys.put(foreinKey.getString("FKCOLUMN_NAME"),
                    foreinKey.getString("PKTABLE_NAME"));
        }
        DBTable table = new DBTable();
        while (tableRet.next()) {
            table.setTableName(tableRet.getString("TABLE_NAME")); // 表名
            table.setRemarks(tableRet.getString("REMARKS"));// 表注释
            ResultSet colRet = databaseMetaData.getColumns(null, "%",
                    table.getTableName(), "%");
            while (colRet.next()) {
                DBColumn column = new DBColumn();
                column.setColumnName(colRet.getString("COLUMN_NAME"));// 列名
                column.setColumnType(colRet.getString("TYPE_NAME"));// 列类型
                column.setNullable(colRet.getInt("NULLABLE") == 1);// 允许空
                column.setSize(colRet.getInt("COLUMN_SIZE"));// 列长度
                column.setRemarks(colRet.getString("REMARKS"));// 注释
                column.setRefTable(forekeys.get(column.getColumnName()));// 外键表
                table.getColumns().put(column.getColumnName(), column);
            }
            colRet.close();
        }
        tableRet.close();
        if (table.getTableName() == null) {
            return null;
        }

        return table;
    }

    /**
     * <b>获取当前数据库中所有表的元数据 </b>
     * <p>
     * 根据表名获取信息
     * </p>
     *
     * @return List
     * @throws SQLException
     */
    public List<DBTable> getTables(String tableName) throws SQLException {
        List<DBTable> dbTables = new ArrayList<>();
        List<String> tables = new ArrayList<>();
        ResultSet tableRet = databaseMetaData.getTables("db-dzt", null, "%"
                + tableName + "%", new String[]{"TABLE"});
        while (tableRet.next()) {
            tables.add(tableRet.getString("TABLE_NAME"));
        }
        tableRet.close();
        tables.forEach(table -> {
            DBTable dbtable;
            try {
                dbtable = getTable(table);
                dbTables.add(dbtable);
            } catch (Exception e) {
                e.printStackTrace();
            }
        });
        return dbTables;
    }

    /**
     * <b>获取当前数据库中所有表的元数据 </b>
     * <p>
     * 获取全部的表格信息
     * </p>
     *
     * @return List
     * @throws SQLException
     */
    public List<DBTable> getTables() throws SQLException {
        return getTables("");
    }

    /**
     * <b> 关闭连接 </b>
     * <p>
     * 关闭当前的数据库连接
     * </p>
     */
    public void colse() {
        try {
            this.connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
